package com.jr.dao.impl;

import com.jr.dao.IUsersEmployeesDao;
import com.jr.pojo.Employees;
import com.jr.pojo.Users;
import com.jr.pojo2.UsersEmployees;
import com.jr.util.DBHelper;
import com.jr.util.PageHelper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class UsersEmployeesDaoImpl implements IUsersEmployeesDao {

    DBHelper db = new DBHelper();

    @Override // 分页查询 -- 总条数
    public int selectPageDataCount() {
        StringBuilder sql =new StringBuilder("select count(id) from users where 1=1");
        Connection con =null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            con = db.getCon();
            ps = con.prepareStatement(sql.toString());
            resultSet = ps.executeQuery();
            if (resultSet.next()) {
                return resultSet.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            db.closeAll(con,ps,resultSet);
        }
        return 0;
    }

    @Override // 分页查询 查询当前页面 展示集合
    public ArrayList<UsersEmployees> selectIndexDate(PageHelper ph) {
        /*String userName, String realName, String email,
          String phoneNumber, String role, Timestamp createdAt */
        /*String sql="select u.userName,e.name,u.email,u.phoneNumber,u.role,u.createdAt" +
                " from users u inner join employees e on u.employeeId=e.employeeId limit ?,?";*/
        String sql = "select u.*,e.name from users u inner join employees e on u.employeeId=e.employeeId limit ?,?";
        int index = (ph.getPageIndex()-1)*ph.getShowNumber();
        return db.dtQuery(sql, UsersEmployees.class, index, ph.getShowNumber());

    }


    @Override // 查询一条
    public UsersEmployees selectUsersEmployeesByUserName(String userName) {
        /*String userName, String realName, String email,
          String phoneNumber, String role, Timestamp createdAt*/
        String sql = "select u.*,e.name from users u inner join employees e on u.employeeId=e.employeeId where u.userName=?";
        ArrayList<UsersEmployees> listUE = db.dtQuery(sql, UsersEmployees.class, userName);
        return listUE.size()>0?listUE.get(0):null;
    }

    @Override // 分页查询 -- 按名字 查 -- 总条数
    public int selectPageDataCountByUserName(String search) {
        StringBuilder sql = new StringBuilder("select count(userName) from users where userName like ? or email like ? or phoneNumber like?");
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            con = db.getCon();
            ps = con.prepareStatement(sql.toString());

            ps.setString(1, "%" + search + "%");
            ps.setString(2, "%" + search + "%");
            ps.setString(3, "%" + search + "%");

            resultSet = ps.executeQuery();
            if (resultSet.next()) {
                return resultSet.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            db.closeAll(con, ps, resultSet);
        }
        return 0;
    }

    @Override // 分页查询 按名字 查询 展示集合
    public ArrayList<UsersEmployees> selectIndexDateByUserName(String search, PageHelper ph) {
        String sql = "select u.*,e.name from users u inner join employees e on u.employeeId=e.employeeId where userName like ? or email like ? or phoneNumber like ? limit ?,?";
        int index = ((ph.getPageIndex() - 1) * ph.getShowNumber());

        return db.dtQuery(sql, UsersEmployees.class, "%"+search+"%","%"+search+"%","%"+search+"%", index, ph.getShowNumber());
    }
/*
    @Override // 分页查询 -- 按邮箱 查 -- 总条数
    public int selectPageDataCountByEmail(String email) {
        StringBuilder sql = new StringBuilder("select count(email) from users where email like ?");
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            con = db.getCon();
            ps = con.prepareStatement(sql.toString());
            ps.setObject(1, "%"+email+"%");
            resultSet = ps.executeQuery();
            if (resultSet.next()) {
                return resultSet.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            db.closeAll(con, ps, resultSet);
        }
        return 0;
    }


    @Override // 分页查询 按邮箱 查询 展示集合
    public ArrayList<UsersEmployees> selectIndexDateByEmail(String email, PageHelper ph) {
        String sql = "select u.*,e.name from users u inner join employees e on u.employeeId=e.employeeId where email like ? limit ?,?";
        int index = (ph.getPageIndex() - 1) * ph.getShowNumber();
        return db.dtQuery(sql, Employees.class, "%"+email+"%", index, ph.getShowNumber());
    }

    @Override // 分页查询 -- 按手机号 查 -- 总条数
    public int selectPageDataCountByPhoneNum(String phoneNumber) {
        StringBuilder sql = new StringBuilder("select count(phoneNumber) from users where phoneNumber like ?");
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            con = db.getCon();
            ps = con.prepareStatement(sql.toString());
            ps.setObject(1, "%"+phoneNumber+"%");
            resultSet = ps.executeQuery();
            if (resultSet.next()) {
                return resultSet.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            db.closeAll(con, ps, resultSet);
        }
        return 0;
    }

    @Override // 分页查询 按手机号 查询 展示集合
    public ArrayList<UsersEmployees> selectIndexDateByPhoneNum(String phoneNumber, PageHelper ph) {
        String sql = "select u.*,e.name from users u inner join employees e on u.employeeId=e.employeeId where phoneNumber like ? limit ?,?";
        int index = (ph.getPageIndex() - 1) * ph.getShowNumber();
        return db.dtQuery(sql, Employees.class, "%"+phoneNumber+"%", index, ph.getShowNumber());
    }*/
}
